package com.ruicar.afs.cloud.batch.dayend.process;

import com.ruicar.afs.cloud.batch.dayend.vo.DebitInfoVO;
import com.ruicar.afs.cloud.batch.dayend.vo.PenaltyInterestVO;
import com.ruicar.afs.cloud.batch.frame.AbsBatchRunner;
import com.ruicar.afs.cloud.common.core.exception.AfsBaseException;
import com.ruicar.afs.cloud.common.core.uid.UidGenerator;
import com.ruicar.afs.cloud.common.core.util.SpringContextHolder;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @description: 借据信息逾期金额批量任务
 * @author: sijun.yu
 * @date: 2020/8/11 16:37
 */
public class OverdueAmountBatchRunner extends AbsBatchRunner {

    public static final int TOTAL_NUM = 800;
    UidGenerator uidGenerator = SpringContextHolder.getBean(UidGenerator.class);

    @Override
    public void process(String param){

        Connection base = this.getConnection();
        Connection contract = this.getConnection("contract");
        logger.info("run 执行========={}-{}-{}",this.getSystemName(),this.getBatchNo(),this.getStepNo());
        try{
            // 更新借据信息
            updateDebitOverdueAmt(contract);
            // 更新费用表
            saveOrUpdateExpenseRecord(contract);
        }catch (Exception e){
            logger.error("",e);
            throw new AfsBaseException(e.getMessage());
        }finally {
            clean(contract);
            clean(base);
        }

    }

    /**
     * 查询借据信息
     * @param connection
     * @return
     * @throws Exception
     */
    public void updateDebitOverdueAmt(Connection connection) throws Exception{

        StringBuffer sql = new StringBuffer();
        sql.append("select debit.contract_no,max(interest.overdue_days) as max_overdue_days,sum(ifnull(interest.overdue_amt,0)) as overdue_amt,info.grace_period_days,debit.history_big_overdue ");
        sql.append("from basic_penalty_interest interest ");
        sql.append("inner join basic_main_info info on info.contract_no = interest.contract_no ");
        sql.append("inner join basic_debit_info debit on debit.contract_no = interest.contract_no ");
        sql.append("group by debit.contract_no,info.grace_period_days,debit.history_big_overdue ");
        // 更新借据信息
        StringBuffer updateSql = new StringBuffer();
        updateSql.append("update basic_debit_info set overdue_total_amount = ?,is_overdue = ?,history_big_overdue=?,current_absolute_overdue=?,current_relative_overdue=? ");
        updateSql.append("where contract_no = ? ");
        // 更新合同状态
        StringBuffer updateSql2 = new StringBuffer();
        updateSql2.append("update basic_main_info set contract_status = 'overdue' where contract_no = ?");

        PreparedStatement ps = null;
        ResultSet rs = null;
        PreparedStatement updatePs = null;
        PreparedStatement updatePs2 = null;
        int updateCount = 0;
        int updateCount2 = 0;

        try {
            // 更新借据信息
            updatePs = connection.prepareStatement(updateSql.toString());
            // 更新合同状态
            updatePs2 = connection.prepareStatement(updateSql2.toString());
            // 查询
            ps = connection.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while(rs!=null && rs.next()){

                DebitInfoVO vo = new DebitInfoVO();
                vo.setContractNo(rs.getString("contract_no"));
                vo.setMaxOverdueDays(rs.getInt("max_overdue_days"));
                vo.setOverdueTotalAmount(rs.getBigDecimal("overdue_amt"));
                vo.setGracePeriodDays(rs.getInt("grace_period_days"));
                vo.setHistoryBigOverdue(rs.getInt("history_big_overdue"));

                // 更新借据信息
                updatePs.setBigDecimal(1,vo.getOverdueTotalAmount());
                updatePs.setString(2,"yes");// 逾期
                Integer historyBigOverdue = vo.getHistoryBigOverdue();
                if(historyBigOverdue!=null && historyBigOverdue<vo.getMaxOverdueDays()){
                    historyBigOverdue = vo.getMaxOverdueDays();
                }
                updatePs.setInt(3,historyBigOverdue);
                updatePs.setInt(4,vo.getMaxOverdueDays());
                updatePs.setInt(5,vo.getMaxOverdueDays() - vo.getGracePeriodDays());
                updatePs.setString(6,vo.getContractNo());
                updatePs.addBatch();
                updateCount++;
                if(updateCount%TOTAL_NUM == 0){
                    updatePs.executeBatch();
                    connection.commit();
                }

                // 更新合同状态
                updatePs2.setString(1, vo.getContractNo());
                updatePs2.addBatch();
                updateCount2++;
                if(updateCount2%TOTAL_NUM == 0){
                    updatePs2.executeBatch();
                    connection.commit();
                }
            }
            if(updateCount%TOTAL_NUM != 0){
                updatePs.executeBatch();
                connection.commit();
            }
            if(updateCount2%TOTAL_NUM != 0){
                updatePs2.executeBatch();
                connection.commit();
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        }finally {
            clean(ps);
            clean(rs);
            clean(updatePs);
            clean(updatePs2);
        }
    }

    /**
     * 同步罚息金额到费用表
     * @param connection
     * @return
     * @throws Exception
     */
    public void saveOrUpdateExpenseRecord(Connection connection) throws Exception{

        // 获取罚息汇总金额
        StringBuffer sql = new StringBuffer();
        sql.append("select contract_no,sum(total_penalty) total_penalty,sum(received_amt) received_amt from basic_penalty_interest ");
        sql.append("where total_penalty >= received_amt ");
        sql.append("group by contract_no");
        // 查询是否存在
        StringBuffer querySql = new StringBuffer();
        querySql.append("select contract_no,received_amt,exemption_amt from basic_expense_record ");
        querySql.append("where cost_type = 'penalty' and contract_no = ? ");
        // 保存
        StringBuffer insertSql = new StringBuffer();
        insertSql.append("insert into basic_expense_record(id,contract_no,cost_type,cost_amt,status,create_by,create_time,del_flag,remaining_amt) ");
        insertSql.append("values(?,?,?,?,?,?,?,?,?)");
        // 更新
        StringBuffer updateSql = new StringBuffer();
        updateSql.append("update basic_expense_record set cost_amt = ?, update_time = ?, remaining_amt = ?, status = ? " );
        updateSql.append("where contract_no = ? and cost_type = 'penalty' ");

        PreparedStatement ps = null;
        ResultSet rs = null;
        Date batchDate = new Date(batchDateInfo.getBatchDate().getTime());

        PreparedStatement queryPs = null;
        PreparedStatement insertPs = null;
        PreparedStatement updatePs = null;
        ResultSet rs2 = null;
        int insertCount = 0;
        int updateCount = 0;

        try{
            // 更新
            updatePs = connection.prepareStatement(updateSql.toString());
            // 新增
            insertPs = connection.prepareStatement(insertSql.toString());
            // 查询
            ps = connection.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while(rs!=null && rs.next()){

                PenaltyInterestVO vo = new PenaltyInterestVO();
                vo.setContractNo(rs.getString("contract_no"));
                vo.setTotalPenalty(rs.getBigDecimal("total_penalty"));
                vo.setReceivedAmt(rs.getBigDecimal("received_amt"));
                // 查询
                queryPs = connection.prepareStatement(querySql.toString());
                queryPs.setString(1,vo.getContractNo());
                rs2 = queryPs.executeQuery();
                if(rs2!=null){
                    // 更新数据
                    if(rs2.next()){
                        updatePs.setBigDecimal(1,vo.getTotalPenalty());
                        updatePs.setDate(2,batchDate);
                        BigDecimal receivedAmt = rs2.getBigDecimal("received_amt");// 已收金额
                        BigDecimal exemptionAmt = rs2.getBigDecimal("exemption_amt");// 实际豁免金额
                        BigDecimal remainingAmt = vo.getTotalPenalty().subtract(receivedAmt).subtract(exemptionAmt);
                        if(remainingAmt.compareTo(BigDecimal.ZERO)<=0){
                            remainingAmt = BigDecimal.ZERO;
                        }
                        updatePs.setBigDecimal(3,remainingAmt);
                        updatePs.setString(4,"unsettle");
                        updatePs.setString(5,vo.getContractNo());
                        updatePs.addBatch();
                        updateCount++;
                        if(updateCount%TOTAL_NUM == 0){
                            updatePs.executeBatch();
                            connection.commit();
                        }
                    }
                    // 插入数据
                    else{
                        insertPs.setLong(1,uidGenerator.getUID());
                        insertPs.setString(2,vo.getContractNo());
                        insertPs.setString(3,"penalty");
                        insertPs.setBigDecimal(4,vo.getTotalPenalty());
                        insertPs.setString(5,"unsettle");
                        insertPs.setString(6,"system");
                        insertPs.setDate(7,batchDate);
                        insertPs.setString(8,vo.getDelFlag());
                        insertPs.setBigDecimal(9,vo.getTotalPenalty());
                        insertPs.addBatch();
                        insertCount++;
                        if(insertCount%TOTAL_NUM == 0){
                            insertPs.executeBatch();
                            connection.commit();
                        }
                    }
                }
            }
            // 更新数据
            if(updateCount%TOTAL_NUM != 0){
                updatePs.executeBatch();
                connection.commit();
            }
            // 插入数据
            if(insertCount%TOTAL_NUM != 0){
                insertPs.executeBatch();
                connection.commit();
            }
        }catch (Exception e){
            logger.error("", e);
            throw new AfsBaseException(e.getMessage());
        }finally {
            clean(ps);
            clean(rs);
            clean(queryPs);
            clean(insertPs);
            clean(updatePs);
            clean(rs2);
        }
    }

}
